--EXEC sp_lot_history_by_lot_number 1

ALTER  proc sp_lot_history_by_lot_number
@PRODUCT_ID INT
AS
begin

declare @results Table( Wafer_Lot_No varchar(50),
			Fab_out_Wafers 	INT,
			DIE_REV 	INT,
			WAFER_OUTDATE 	DATETIME,
			WAFERSORT_DATE DATETIME,
			WAFERSORT_TEST_PGM	varchar(50),
			TOTAL_GOOD_DIE_QTY 	int,
			WAFERSORT_YILED	FLOAT,
			MARKING	VARCHAR(100),
			ASSEMBLY_LOT_NOS VARCHAR(50),
			ASSEMBLY_IN_QTY	 INT,
			ASSEMBLY_YIELD	FLOAT,
			TEST_QTY_IN		INT,
			TEST_DATE_IN    DATETIME,
			BIN1			INT,
			BIN2			INT,
			BIN3			INT,
			BIN4			INT,
			BIN5			INT,
			GOOD_UNITS		INT,
			UNCONUT_UNITS	INT,
			TEST_YIELD		FLOAT,
			TESTER			VARCHAR(50),
			TEST_PGM		VARCHAR(50),
			SCAN_OUTQTY		INT,
			SCAN_YIELD		FLOAT,
			CUM_YIELD		FLOAT
			 )

DECLARE @LOT_NUMBER_TABLE TABLE ( Fab_Lot_Number VARCHAR(50), TIMESTAMP DATETIME, PROCESSSTEP_ID INT )

declare @lot_id_cursor 	 CURSOR
declare @aasy_lot_cursor CURSOR
declare @test_lot_cursor CURSOR

declare @fab_lot VARCHAR(50)
declare @PS_TYPE VARCHAR(50)
DECLARE @PROCESSSTEP_ID INT

declare	@fab_date_out DATETIME, @SORT_DATE_OUT DATETIME, @TEST_DATEIN DATETIME
DECLARE @fab_out_qty INT, @GROSS_DIE INT, @GOOD_DIE INT ,@TOTALASSY_INQTY INT, @ASSY_QTY INT, @TOTALTEST_INQTY INT, @TEST_QTY INT, @SCAN_OUTQTY INT
DECLARE @SORTTEST_PGM VARCHAR(50), @TEST_PGM VARCHAR(50), @TESTER VARCHAR(50)
DECLARE @SORT_YIELD FLOAT, @ASSY_YIELD FLOAT, @TEST_YIELD FLOAT, @SCAN_YIELD FLOAT,@CUM_YIELD FLOAT
DECLARE @BIN1 INT,@BIN2 INT,@BIN3 INT,@BIN4 INT,@BIN5 INT, @GOOD_UNITS INT, @UNCOUNT_UNITS INT
DECLARE @BIN1_QTY INT,@BIN2_QTY INT,@BIN3_QTY INT,@BIN4_QTY INT,@BIN5_QTY INT, @DAMAGE_QTY INT
DECLARE @ASSEMBLY_LOT VARCHAR(50),@ASSY_LOT VARCHAR(50)
DECLARE @MARKING VARCHAR(100),@PART_NUMBER VARCHAR(50), @DATE_CODE VARCHAR(50)
DECLARE @TID VARCHAR(50),@TPGM VARCHAR(50), @PART_NO VARCHAR(50), @DCODE VARCHAR(50)

IF @PRODUCT_ID > 0
BEGIN
	INSERT INTO @LOT_NUMBER_TABLE
	SELECT distinct Fab_Lot_Number, LT.TIMESTAMP, PS.PROCESSSTEP_ID FROM LOT_TABLE LT, PROCESS_STEP PS,  PRODUCT P, SHIPPED_LOT SL WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID= SL.LOT_ID AND P.P_PARENT_PRODUCT_ID = @PRODUCT_ID AND PS_TYPE_NAME IN ('FAB')
END
ELSE
BEGIN
	INSERT INTO @LOT_NUMBER_TABLE
	SELECT distinct Fab_Lot_Number, LT.TIMESTAMP, PS.PROCESSSTEP_ID  FROM LOT_TABLE LT, PROCESS_STEP PS, SHIPPED_LOT SL WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID= SL.LOT_ID  AND PS_TYPE_NAME IN ('FAB')
END
--SELECT * FROM @LOT_NUMBER_TABLE	ORDER BY TIMESTAMP

set @lot_id_cursor = CURSOR FOR SELECT Fab_Lot_Number, PROCESSSTEP_ID FROM @LOT_NUMBER_TABLE ORDER BY TIMESTAMP

open @lot_id_cursor
	fetch next from @lot_id_cursor into @FAB_LOT, @PROCESSSTEP_ID
	while @@fetch_status = 0
	begin
			
		--SELECT * FROM PROCESS_PARAMETER
		SELECT @GROSS_DIE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = 'Gross Die/Wafer'
		PRINT @GROSS_DIE
		--select @PS_TYPE = PS_Type from Product, Process_Step where Process_Step.ProcessStep_ID = Product.ProcessStep_ID
		
		--if @ps_type = 'FAB'
		--begin
				--PRINT @ps_type
				--SELECT * FROM LOT_TABLE
				SELECT @FAB_OUT_QTY = SHIP_QTY, @FAB_DATE_OUT = ISNULL( SL.SHIP_DATE, SL.TIMESTAMP ) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE LT.LOT_ID= SL.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT
				SET @SORTTEST_PGM = ''
				SELECT @SORT_DATE_OUT = DATE_IN, @GOOD_DIE = GOOD_DIE_QTY, @SORTTEST_PGM = SL_TEST_PROGRAM, @SORT_YIELD = SL_YIELD FROM SORT_LOT SRL, LOT_TABLE LT WHERE LT.LOT_ID= SRL.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT

				--SELECT @ASSEMBLY_LOT  = LOT_NUMBER FROM LOT_TABLE LT, ASSEMBLY_LOT AL WHERE LT.LOT_ID= AL.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT
				SET @TOTALASSY_INQTY = 0
				set @ASSEMBLY_LOT = ''
				set @aasy_lot_cursor = CURSOR FOR SELECT LOT_NUMBER FROM LOT_TABLE LT, ASSEMBLY_LOT AL WHERE LT.LOT_ID= AL.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT
				open @aasy_lot_cursor
				fetch next from @aasy_lot_cursor into @ASSY_LOT 
				while @@fetch_status = 0
				begin								
						SELECT TOP 1 @ASSY_QTY  = QTY_IN FROM LOT_TABLE_HISTORY LTH, ASSEMBLY_LOT AL WHERE LTH.LOT_ID= AL.LOT_ID AND LOT_NUMBER = @ASSY_LOT ORDER BY LTH.TIMESTAMP	
						IF @ASSY_QTY IS NULL
						BEGIN
							SELECT @ASSY_QTY = QTY_IN FROM LOT_TABLE LT, ASSEMBLY_LOT AL WHERE LT.LOT_ID= AL.LOT_ID AND LOT_NUMBER = @ASSY_LOT 
						END
						
						SET @TOTALASSY_INQTY = @TOTALASSY_INQTY + @ASSY_QTY
					
						SET @ASSEMBLY_LOT = @ASSEMBLY_LOT + @ASSY_LOT + ', '

				set @ASSY_LOT = NULL
				set @ASSY_QTY = null
				fetch next from @aasy_lot_cursor into @ASSY_LOT
				end
				close @aasy_lot_cursor
				deallocate @aasy_lot_cursor

				--SELECT TOP 1 @TOTALASSY_INQTY  = SUM(QTY_IN) FROM LOT_TABLE_HISTORY LTH, ASSEMBLY_LOT AL WHERE LTH.LOT_ID= AL.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT ORDER BY LTH.TIMESTAMP

				/*IF @TOTALASSY_INQTY IS NULL
				BEGIN
						SELECT @TOTALASSY_INQTY = SUM(QTY_IN) FROM LOT_TABLE LT, ASSEMBLY_LOT AL WHERE LT.LOT_ID= AL.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT
				END*/
				
				/*SELECT @TEST_INQTY  = SUM(QTY_IN), @TEST_DATEIN = MIN(DATE_IN), @MARKING = PART_NUMBER +'/n' + DATE_CODE , /*@TEST_PGM = FTL_TEST_PROGRAM, @TESTER = TESTER_ID ,*/ @BIN1 = SUM(BIN_1), @BIN2 = SUM(BIN_2), @BIN3 = SUM(BIN_3), @BIN4 = SUM(BIN_4), @BIN5 = SUM(BIN_5),@UNCONUT_UNITS = SUM(DAMAGE_QTY) + SUM(LOSS_QTY)
						FROM LOT_TABLE LT, Product P, PROCESS_STEP PS, FINAL_TEST_LOT FTL,TEST_INFORMATIVE_BINNING TIB 
						WHERE PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.ProcessStep_ID = LT.ProcessStep_ID AND PS.PS_TYPE = 'FT' AND LT.LOT_ID= FTL.LOT_ID AND FTL.LOT_ID = TIB.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT GROUP BY PART_NUMBER, DATE_CODE,FTL_TEST_PROGRAM, TESTER_ID*/
				set @test_lot_cursor = CURSOR FOR SELECT SUM(QTY_IN), MAX(DATE_IN), PART_NUMBER, DATE_CODE, FTL_TEST_PROGRAM, TESTER_ID, SUM(BIN_1), SUM(BIN_2),SUM(BIN_3),SUM(BIN_4), SUM(BIN_5),SUM(ISNULL(DAMAGE_QTY, 0) + ISNULL(LOSS_QTY,0) )
												FROM LOT_TABLE LT, Product P, PROCESS_STEP PS, FINAL_TEST_LOT FTL,TEST_INFORMATIVE_BINNING TIB 
												WHERE PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.ProcessStep_ID = LT.ProcessStep_ID AND PS.PS_TYPE = 'FT' AND LT.LOT_ID= FTL.LOT_ID AND FTL.LOT_ID = TIB.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT  GROUP BY PART_NUMBER, DATE_CODE, FTL_TEST_PROGRAM, TESTER_ID
				SET @TOTALTEST_INQTY = 0
				SET @BIN1 = 0 SET @BIN2 = 0  SET @BIN3 = 0 SET @BIN4 = 0 SET @BIN5 = 0 SET @UNCOUNT_UNITS = 0
				SET @MARKING = '' SET @PART_NUMBER = '' SET @DATE_CODE = '' SET @TEST_PGM = '' SET @TESTER = ''
				open @test_lot_cursor
				fetch next from @test_lot_cursor into @TEST_QTY, @TEST_DATEIN, @PART_NO, @DCODE, @TPGM, @TID,@BIN1_QTY,@BIN2_QTY,@BIN3_QTY,@BIN4_QTY,@BIN5_QTY,@DAMAGE_QTY
				while @@fetch_status = 0
				begin		
							SET @TOTALTEST_INQTY = @TOTALTEST_INQTY + @TEST_QTY
							SET @BIN1			 = @BIN1 + @BIN1_QTY
							SET @BIN2			 = @BIN2 + @BIN2_QTY
							SET @BIN3			 = @BIN3 + @BIN3_QTY
							SET @BIN4			 = @BIN4 + @BIN4_QTY
							SET @BIN5			 = @BIN5 + @BIN5_QTY
							SET @UNCOUNT_UNITS	 = @UNCOUNT_UNITS + @DAMAGE_QTY

							IF  charindex(@PART_NO, @PART_NUMBER) = 0
							BEGIN
								  SET @PART_NUMBER = @PART_NUMBER + @PART_NO + ', '	
							END
							IF  charindex(@DCODE, @DATE_CODE) = 0
							BEGIN
								  SET @DATE_CODE = @DATE_CODE + @DCODE + ', '	
							END
							IF  charindex(@TPGM, @TEST_PGM) = 0
							BEGIN
								  SET @TEST_PGM = @TEST_PGM + @TPGM + ', '	
							END
							IF  charindex(@TID, @TESTER) = 0
							BEGIN
								  SET @TESTER = @TESTER + @TID + ', '	
							END

							SET @MARKING =  @PART_NUMBER + ' \n ' + @DATE_CODE

				SET @TEST_QTY = NULL 
				--SET @TEST_DATEIN = NULL 
				SET @PART_NO = NULL  
				SET @DCODE = NULL  
				SET @TPGM = NULL  
				SET @TID = NULL  
				SET @BIN1_QTY = NULL 
				SET @BIN2_QTY = NULL 
				SET @BIN3_QTY = NULL 
				SET @BIN4_QTY = NULL 
				SET @BIN5_QTY = NULL 
				SET @DAMAGE_QTY = NULL 			
				fetch next from @test_lot_cursor into @TEST_QTY, @TEST_DATEIN , @PART_NO, @DCODE, @TPGM, @TID,@BIN1_QTY,@BIN2_QTY,@BIN3_QTY,@BIN4_QTY,@BIN5_QTY,@DAMAGE_QTY
				end

				close @test_lot_cursor
				deallocate @test_lot_cursor

				IF @TOTALTEST_INQTY IS NOT NULL AND @TOTALASSY_INQTY > 0
				BEGIN
						SET @ASSY_YIELD = ROUND( ( @TOTALTEST_INQTY * 100.00 ) / @TOTALASSY_INQTY , 2)	
				END
				
				IF @BIN2 IS NOT NULL 
				BEGIN
						SET @GOOD_UNITS = @BIN1+@BIN2+@BIN3
						IF @TOTALTEST_INQTY > 0
						BEGIN
							SET @TEST_YIELD = ROUND( ( @GOOD_UNITS * 100.00 ) / @TOTALTEST_INQTY , 2)
						END
				END

				SELECT @SCAN_OUTQTY = SUM(QTY_OUT) FROM LOT_TABLE LT, PROCESS_STEP PS, FINAL_TEST_LOT FTL
						WHERE PS.ProcessStep_ID = LT.ProcessStep_ID AND PS.PS_TYPE = 'Ball Scan' AND LT.LOT_ID= FTL.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT

				IF @SCAN_OUTQTY IS NOT NULL
				BEGIN
						SET @SCAN_YIELD = ROUND( ( @SCAN_OUTQTY * 100.00 ) / @GOOD_UNITS , 2)
						SET @CUM_YIELD  = ROUND( ( @SCAN_OUTQTY * 100.00 ) / (@GROSS_DIE * @FAB_OUT_QTY) , 2)
				END

				insert @results ( Wafer_Lot_No, Fab_out_Wafers, DIE_REV, WAFER_OUTDATE, WAFERSORT_DATE, WAFERSORT_TEST_PGM, TOTAL_GOOD_DIE_QTY, WAFERSORT_YILED, MARKING, ASSEMBLY_LOT_NOS, ASSEMBLY_IN_QTY, ASSEMBLY_YIELD, TEST_QTY_IN, TEST_DATE_IN,BIN1,BIN2,BIN3,BIN4,BIN5, GOOD_UNITS, UNCONUT_UNITS, TEST_YIELD, TESTER, TEST_PGM, SCAN_OUTQTY, SCAN_YIELD, CUM_YIELD)
					values( @fab_lot, @fab_out_qty, null, convert(varchar,@fab_date_out,101), convert(varchar,@fab_date_out,101), @SORTTEST_PGM, @GOOD_DIE, @SORT_YIELD,@MARKING,@ASSEMBLY_LOT,@TOTALASSY_INQTY,@ASSY_YIELD, @TOTALTEST_INQTY, convert(varchar,@TEST_DATEIN,101), @BIN1,@BIN2,@BIN3,@BIN4,@BIN5, @GOOD_UNITS , @UNCOUNT_UNITS, @TEST_YIELD, @TESTER, @TEST_PGM, @SCAN_OUTQTY,@SCAN_YIELD, @CUM_YIELD )
			--SELECT * FROM @results
		--END

		set @ps_type		= null
		SET @fab_out_qty	= null
		SET @fab_date_out	= null
		SET @SORT_DATE_OUT	= null
		SET @GOOD_DIE		= null
		SET @GROSS_DIE		= null
		SET @SORTTEST_PGM	= null
		SET @SORT_YIELD		= null
		SET @ASSEMBLY_LOT	= null
		SET @TOTALASSY_INQTY= null
		SET @ASSY_YIELD		= null
		SET @TOTALTEST_INQTY= null
		SET @TEST_DATEIN	= null
		SET @MARKING		= null
		SET @TEST_PGM		= null
		SET @BIN1			= null
		SET @BIN2			= null
		SET @BIN3			= null
		SET @BIN4			= null
		SET @BIN5			= null
		SET @GOOD_UNITS		= null
		SET @UNCOUNT_UNITS  = null
		SET @TEST_YIELD		= null
		SET @TESTER			= null
		SET @SCAN_OUTQTY	= null
		SET @SCAN_YIELD		= NULL
		SET @CUM_YIELD		= null
		SET @fab_lot		= null
		SET @PROCESSSTEP_ID = null

		FETCH NEXT FROM @LOT_ID_CURSOR INTO @FAB_LOT,@PROCESSSTEP_ID 
	end

close @lot_id_cursor
deallocate @lot_id_cursor

SELECT * FROM @results order by WAFER_OUTDATE
end


